{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 总任务\n",
    "针对“发票样例”中的以压缩文件形式存放的发票文件\\\n",
    "\n",
    "1 通过python 解压成单独的pdf文件格式的电子发票文件\\\n",
    "2 读取pdf文件中的信息，在mysql数据库中建立相应的数据表，将读取的信息存入数据表中\\\n",
    "3 建立web服务器，连接第2步得到的mysql数据库，为用户提供发票的查询服务与下载服务，将满足查询条件的多张发票打包下载\\\n",
    "4 批量打印满足查询条件的多张发票\n",
    "\n",
    "### （1）读取pdf文件并提取信息\n",
    "参考博客：https://www.jianshu.com/p/65eae86116c9\n",
    "\n",
    "读取pdf文件，使用到pdfplumber库。读取出的文本内容使用正则匹配来获取信息。使用之前需要使用pip命令安装该库。\n",
    "\n",
    "这里匹配的是invoice文件夹中的发票信息。没有匹配invoiceDetail里面的信息。\n",
    "!pip install pdfplumber\n",
    "\n",
    "关于PDF文件的读取：\n",
    "- pdfplumber.open()：打开pdf文件\n",
    "- pdf.pages[0]：查看第一页的内容\n",
    "- first_page.extract_text()：读取文本信息\n",
    "\n",
    "这里对PDF文件信息的提取使用的是正则匹配，会用到```re```库。\n",
    "关于库里的函数可以参考：https://blog.csdn.net/qq_39962271/article/details/123884585\n",
    "\n",
    "对于表格里面的信息可以使用```extract_table```提取。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pdfplumber\n",
    "import re\n",
    "import os\n",
    "import pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "\n",
    "# 简单写法\n",
    "def get_pdf(dir_path):\n",
    "    pdf_file = []\n",
    "    for root, sub_dirs, file_names in os.walk(dir_path): # 返回值:(dirpath, dirnames, filenames)\n",
    "        for name in file_names:\n",
    "            if name.endswith('.pdf'):\n",
    "                filepath = os.path.join(root, name)\n",
    "                pdf_file.append(filepath)\n",
    "    return pdf_file\n",
    "\n",
    "def read(pdf_dir):\n",
    "    pdfs = []\n",
    "    filenames = get_pdf(pdf_dir)  # 修改为自己的文件目录\n",
    "    for filename in filenames:\n",
    "        pdf_f = {}\n",
    "        with pdfplumber.open(filename) as pdf:\n",
    "            first_page = pdf.pages[0]\n",
    "            pdf_text = first_page.extract_text() # 提取文本信息\n",
    "            pdf_table = first_page.extract_table() # 提取表格信息\n",
    "            pdf_table = pandas.DataFrame(pdf_table)\n",
    "            pdf_text = pdf_text.replace(' ', '').replace('　', '').replace('）', '').replace(')', '').replace('：', ':')\n",
    "            # 正则匹配\n",
    "            pdf_f[\"t1\"] = re.search(r'[\\u4e00-\\u9fa5]+电子普通发票.*?',pdf_text).group()\n",
    "            t2 = re.match(r'[\\u4e00-\\u9fa5]+专用发票.*?', pdf_text)\n",
    "            if t2:\n",
    "                pdf_f[\"t2\"] = t2.group()\n",
    "            pdf_f[\"pro_name\"] = pdf_table.loc[1][0].replace(\"\\n\",\"\").replace(\"合 计\",\"\")[4:] # 项目名称\n",
    "            pdf_f[\"code\"] = re.findall(r'发票代码:(.*\\d+)', pdf_text)[0] # 发票代码\n",
    "            pdf_f[\"num\"] = re.findall(r'发票号码:(.*\\d+)', pdf_text)[0] # 发票号码\n",
    "            pdf_f[\"date\"] = re.findall(r'开票日期:(.*)', pdf_text)[0] # 开票日期\n",
    "            pdf_f[\"year\"] = re.findall(r\"([0-9]+\\.?[0-9]+)\",pdf_f[\"date\"])[0]\n",
    "            pdf_f[\"month\"] = re.findall(r\"([0-9]+\\.?[0-9]+)\",pdf_f[\"date\"])[1]\n",
    "            pdf_f[\"day\"] = re.findall(r\"([0-9]+\\.?[0-9]+)\",pdf_f[\"date\"])[2]\n",
    "            pdf_f[\"client_name\"] = re.findall(r'名\\s*称\\s*[:]\\s*([\\u4e00-\\u9fa5]+)', pdf_text)[0] # 购买方名称\n",
    "            pdf_f[\"client_itin\"] = re.findall(r'纳税人识别号\\s*[:]\\s*([a-zA-Z0-9]+)', pdf_text)[0] # 购买方税号\n",
    "            pdf_f[\"seller_name\"] = re.findall(re.compile(r'名.*称\\s*[:]\\s*([\\u4e00-\\u9fa5]+)'), pdf_text)[-1] # 销售方名称\n",
    "            pdf_f[\"seller_itin\"] = re.findall(r'纳税人识别号\\s*[:]\\s*([a-zA-Z0-9]+)', pdf_text)[-1] # 购买方税号\n",
    "            pdf_f[\"car_num\"] = pdf_table.loc[1][2].replace(\"车牌号\\n\",\"\")\n",
    "            pdf_f[\"car_type\"] = pdf_table.loc[1][3].replace(\"类型\\n\",\"\")\n",
    "            pdf_f[\"total_price\"] = re.findall(r\"([0-9]+\\.?[0-9]+)\",pdf_table.loc[2][2])[0]\n",
    "            pdf_f[\"price\"] = re.findall(r\"([0-9]+\\.?[0-9]+)\",pdf_table.loc[1][8])[0]\n",
    "            pdf_f[\"tax_rate\"] = pdf_table.loc[1][9].replace(\"税率\\n\",\"\")\n",
    "            pdf_f[\"tax_price\"] = re.findall(r\"([0-9]+\\.?[0-9]+)\",pdf_table.loc[1][10])[0]\n",
    "            pdf_f[\"dir\"] = filename\n",
    "            pdf_f[\"path\"] = pdf_dir\n",
    "            pdf_f[\"file\"] = filename.replace(pdf_dir,\"\")\n",
    "        pdfs.append(pdf_f)\n",
    "    return pdfs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'t1': '四川增值税电子普通发票',\n",
       "  'pro_name': '*经营租赁*通行费',\n",
       "  'code': '051001900212',\n",
       "  'num': '10023130',\n",
       "  'date': '2020年12月23日',\n",
       "  'year': '2020',\n",
       "  'month': '12',\n",
       "  'day': '23',\n",
       "  'client_name': '四川三足科技有限公司',\n",
       "  'client_itin': '91510903MA6260NF1E',\n",
       "  'seller_name': '成都机场高速公路有限责任公司',\n",
       "  'seller_itin': '91510100633168022E',\n",
       "  'car_num': '川AM38K4',\n",
       "  'car_type': '客车',\n",
       "  'total_price': '9.50',\n",
       "  'price': '9.22',\n",
       "  'tax_rate': '3%',\n",
       "  'tax_price': '0.28',\n",
       "  'dir': '../发票样例/20201223_通行费电子发票_1张/invoice/91510100633168022E_937083446ff948eba0b1f6a46a88ed52.pdf',\n",
       "  'path': '../发票样例/20201223_通行费电子发票_1张/invoice/',\n",
       "  'file': '91510100633168022E_937083446ff948eba0b1f6a46a88ed52.pdf'}]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "simple_path = '../发票样例/20201223_通行费电子发票_1张/invoice/' \n",
    "read(simple_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "共提取 6 张发票的信息.\n"
     ]
    }
   ],
   "source": [
    "base_path = '../发票样例/' \n",
    "dirs = [base_path + file + '/invoice/' for file in os.listdir(base_path) if os.path.isdir(base_path+file)]\n",
    "pdfs = []\n",
    "for dir in dirs:\n",
    "    pdfs.extend(read(dir))\n",
    "print(f\"共提取 {len(pdfs)} 张发票的信息.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### （2）将信息写入MySQL中\n",
    "在连接数据库之前，我们需要使用到一个模块```pymysql```,使用pip命令安装该库：\n",
    "pip install pymysql\n",
    "\n",
    "还需要开启MySQL服务，在命令行中输入：net start mysql 即可\n",
    "如果报错，有两种可能：\n",
    "1. mysql没有加入环境变量，加入环境变量再执行下一步即可\n",
    "2. MySQL不在服务列表中，在管理员模式下的命令行中输入```mysqld -install```\n",
    "\n",
    "下面主要实现更删改查：\n",
    "这个库主要使用SQL命令对数据库进行操作，具体代码如下所示。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据库连接成功！\n",
      "数据库创建成功！\n",
      "数据表创建成功！\n",
      "信息插入成功！\n",
      "数据如下：\n",
      "('四川增值税电子普通发票', '*经营租赁*通行费', '051001900212', '10023130', '2020年12月23日', '2020', '12', '23', '四川三足科技有限公司', '91510903MA6260NF1E', '成都机场高速公路有限责任公司', '91510100633168022E', '川AM38K4', '客车', '9.50', '9.22', '3%', '0.28', '../发票样例/20201223_通行费电子发票_1张/invoice/91510100633168022E_937083446ff948eba0b1f6a46a88ed52.pdf', '../发票样例/20201223_通行费电子发票_1张/invoice/', '91510100633168022E_937083446ff948eba0b1f6a46a88ed52.pdf')\n",
      "('四川增值税电子普通发票', '*经营租赁*通行费', '051001700112', '30161374', '2020年12月23日', '2020', '12', '23', '四川三足科技有限公司', '91510903MA6260NF1E0', '四川成德南高速公路有限责任公司汇总开具', '91510000689940624C', '川AM38K4', '客车', '10.70', '10.39', '3%', '0.31', '../发票样例/20201223_通行费电子发票_2张/invoice/91510000689940624C_97ca15ecff3a4e9b853c62001dc0db55.pdf', '../发票样例/20201223_通行费电子发票_2张/invoice/', '91510000689940624C_97ca15ecff3a4e9b853c62001dc0db55.pdf')\n",
      "('四川增值税电子普通发票', '*经营租赁*通行费', '051001700112', '98778004', '2020年12月23日', '2020', '12', '23', '四川三足科技有限公司', '91510903MA6260NF1E7', '四川成南高速公路有限责任公司汇总开具', '91510000709155317M', '川AM38K4', '客车', '19.22', '18.66', '3%', '0.56', '../发票样例/20201223_通行费电子发票_2张/invoice/91510000709155317M_44308ed89a5c467e9ede236eb1efd8bc.pdf', '../发票样例/20201223_通行费电子发票_2张/invoice/', '91510000709155317M_44308ed89a5c467e9ede236eb1efd8bc.pdf')\n",
      "('四川增值税电子普通发票', '*经营租赁*通行费', '051001700112', '30469015', '2020年12月23日', '2020', '12', '23', '四川三足科技有限公司', '91510903MA6260NF1E27', '四川成渝高速公路股份有限公司成仁分公司汇总开具', '91510000689919120D', '川AM38K4', '客车', '19.64', '19.07', '3%', '0.57', '../发票样例/20201223_通行费电子发票_3张/invoice/91510000689919120D_f3c17dea5202450daa1d6b293390644c.pdf', '../发票样例/20201223_通行费电子发票_3张/invoice/', '91510000689919120D_f3c17dea5202450daa1d6b293390644c.pdf')\n",
      "('四川增值税电子普通发票', '*经营租赁*通行费', '051001700112', '99748157', '2020年12月23日', '2020', '12', '23', '四川三足科技有限公司', '91510903MA6260NF1E', '四川成南高速公路有限责任公司汇总开具', '91510000709155317M', '川AM38K4', '客车', '5.88', '5.71', '3%', '0.17', '../发票样例/20201223_通行费电子发票_3张/invoice/91510000709155317M_05fc60ecab6343deb5d96fb6b94e61f4.pdf', '../发票样例/20201223_通行费电子发票_3张/invoice/', '91510000709155317M_05fc60ecab6343deb5d96fb6b94e61f4.pdf')\n",
      "('四川增值税电子普通发票', '*经营租赁*通行费', '051001900212', '10023131', '2020年12月23日', '2020', '12', '23', '四川三足科技有限公司', '91510903MA6260NF1E', '成都机场高速公路有限责任公司', '91510100633168022E', '川AM38K4', '客车', '9.50', '9.22', '3%', '0.28', '../发票样例/20201223_通行费电子发票_3张/invoice/91510100633168022E_140d21c2dcd3468eaf56816d9190eb5e.pdf', '../发票样例/20201223_通行费电子发票_3张/invoice/', '91510100633168022E_140d21c2dcd3468eaf56816d9190eb5e.pdf')\n"
     ]
    }
   ],
   "source": [
    "import pymysql \n",
    "\n",
    "# （1）连接数据库\n",
    "connect = pymysql.connect(\n",
    "    host='localhost',\n",
    "    user='root',\n",
    "    passwd=\"\",\n",
    "    charset=\"utf8\",\n",
    "    autocommit=True# 这里不设置会出现无法插入值的奇怪bug\n",
    ")\n",
    "\n",
    "cur = connect.cursor() # 创建游标，用于读取数据\n",
    "\n",
    "if connect:\n",
    "    print(\"数据库连接成功！\")\n",
    "\n",
    "# （2）创建数据库\n",
    "create_db = \"CREATE DATABASE IF NOT EXISTS `pdf_info`;\"\n",
    "try:\n",
    "    cur.execute(create_db)\n",
    "    cur.execute(\"use pdf_info\") # 选择数据库\n",
    "except Exception as e:\n",
    "    print(\"数据库创建失败:\",e)\n",
    "else:\n",
    "    print(\"数据库创建成功！\")\n",
    "\n",
    "# （3）创建数据表\n",
    "create_table = \"CREATE TABLE IF NOT EXISTS info (t1 VARCHAR(100),pro_name VARCHAR(100), code VARCHAR(100), num VARCHAR(100),date VARCHAR(100),year VARCHAR(100),month VARCHAR(100),day VARCHAR(100), client_name VARCHAR(100),client_itin VARCHAR(100), seller_name VARCHAR(100),seller_itin VARCHAR(100), car_num VARCHAR(100),car_type VARCHAR(100),total_price VARCHAR(100),price VARCHAR(100),tax_rate VARCHAR(100),tax_price VARCHAR(100),dir VARCHAR(200),path VARCHAR(200),file VARCHAR(200))\"\n",
    "try:\n",
    "    cur.execute(create_table)\n",
    "except Exception as e:\n",
    "    print(\"数据表创建失败:\",e)\n",
    "else:\n",
    "    print(\"数据表创建成功！\")\n",
    "\n",
    "# （4）插入数据\n",
    "try:\n",
    "    for file in pdfs:\n",
    "        insert_data = \"INSERT INTO `info` VALUES('\"+file[\"t1\"]+\"','\"+file[\"pro_name\"]+\"','\"+file[\"code\"]+\"','\"+file[\"num\"]+\"','\"+file[\"date\"]+\"','\"+file[\"year\"]+\"','\"+file[\"month\"]+\"','\"+file[\"day\"]+\"','\"+file[\"client_name\"]+\"','\"+file[\"client_itin\"]+\"','\"+file[\"seller_name\"]+\"','\"+file[\"seller_itin\"]+\"','\"+file[\"car_num\"]+\"','\"+file[\"car_type\"]+\"','\"+file[\"total_price\"]+\"','\"+file[\"price\"]+\"','\"+file[\"tax_rate\"]+\"','\"+file[\"tax_price\"]+\"','\"+file[\"dir\"]+\"','\"+file[\"path\"]+\"','\"+file[\"file\"]+\"');\"\n",
    "        cur.execute(insert_data)\n",
    "except Exception as e:\n",
    "    print(\"数据信息插入失败:\",e)\n",
    "else:\n",
    "    print(\"信息插入成功！\")\n",
    "\n",
    "# （5）查询数据\n",
    "try:\n",
    "    query_info = \"select * from info;\"\n",
    "    cur.execute(query_info)\n",
    "    res = cur.fetchall()\n",
    "except Exception as e:\n",
    "    print(\"数据表创建失败:\",e)\n",
    "else:\n",
    "    print(\"数据如下：\")\n",
    "    for i in res: print(i)\n",
    "\n",
    "# （6）关闭数据库连接\n",
    "cur.close()\n",
    "connect.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.0 ('base')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "d2ac46498bf9d36d8c53ef3ef5f1114d28cdeda33c2544899d6ce51d38e12b9d"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
